﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.IO;
using System.Data.SQLite;

namespace com.uplus.data
{
    /// <summary>
    /// Sqlite数据库访问类[参数前缀为$，连接符为||]
    /// </summary>
    [Serializable]
    public class DASqlite : IDaAccess
    {
        SQLiteConnection conn = null;
        string dbFile = null; // 数据库文件地址

        #region == DASqlite ==
        /// <summary>
        /// 创建/打开基于文件的数据库，并打开
        /// </summary>
        /// <param name="file">绝对路径（文件若不存在则会被创建）</param>
        /// <param name="password">密码，可为空</param>
        public DASqlite(string file, string password)
        {
            FileInfo fo = new FileInfo(file);
            if (!fo.Exists)
            {
                SQLiteConnection.CreateFile(fo.FullName);
            }
            this.dbFile = file;

            string connString = string.Format("Data Source={0};Version=3;", file);
            if (!string.IsNullOrEmpty(password)) connString = connString + string.Format("Password={0};", password);

            CreateConnection(connString);
        }

        /// <summary>
        /// 创建内存数据库，并打开
        /// </summary>
        public DASqlite()
        {
            CreateConnection("Data Source=:memory:;Version=3;Cache Size = 3000;New=True;Pooling=False;Max Pool Size=100;LongNames=0; Timeout=1000; NoTXN=0; SyncPragma=NORMAL; StepAPI=0");
        }

        void CreateConnection(string connString)
        {
            conn = new SQLiteConnection(connString);
            conn.Open();
        }

        ~DASqlite()
        {
            //this.Dispose();
        }
        #endregion

        #region == Dispose ==
        public void Dispose()
        {
            if (conn != null)
            {
                try
                {
                    conn.Close();
                    conn.Dispose();
                }
                catch
                { }
                
            }
        }
        #endregion

        #region == Excute ==
        public int Excute(string sql, Dictionary<string, object> paramValues)
        {
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;

                if (paramValues != null)
                {
                    foreach (KeyValuePair<string, object> item in paramValues)
                    {
                        SQLiteParameter p = new SQLiteParameter();
                        p.ParameterName = "$" + item.Key;
                        p.Value = item.Value;
                        cmd.Parameters.Add(p);
                    }
                }
                return cmd.ExecuteNonQuery();
            }
        }

        public int Excute(string sql)
        {
            return this.Excute(sql, null);
        }

        public int Excute(string sql, string paramName, object paramValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(paramName, paramValue);
            return this.Excute(sql, pm);
        }
        #endregion

        #region == GetValue<T>
        public T GetValue<T>(string sql)
        {
            return this.GetValue<T>(sql, null);
        }

        public T GetValue<T>(string sql, string paramName, object paramValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(paramName, paramValue);
            return this.GetValue<T>(sql, pm);
        }

        public T GetValue<T>(string sql, Dictionary<string, object> paramValues)
        {
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                if (paramValues != null)
                {
                    foreach (KeyValuePair<string, object> item in paramValues)
                    {
                        SQLiteParameter p = new SQLiteParameter();
                        p.ParameterName = "$" + item.Key;
                        p.Value = item.Value;
                        cmd.Parameters.Add(p);
                    }
                }
                object re = cmd.ExecuteScalar();
                re = Convert.ChangeType(re, typeof(T));
                return (T)re;
            }
        }
        #endregion

        #region == GetValueTable<T>
        public T GetValueTable<T>(string table, string colName)
        {
            return this.GetValueTable<T>(table, colName, null);
        }

        public T GetValueTable<T>(string table, string colName, string colWhereName, object colWhereValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(colWhereName, colWhereValue);
            return this.GetValueTable<T>(table, colName, pm);
        }

        public T GetValueTable<T>(string table, string colName, Dictionary<string, object> colValues)
        {
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("select {0} from {1}", colName, table);
                cmd.CommandType = CommandType.Text;
                if (colValues != null)
                {
                    sb.Append(" where 1=1");
                    foreach (KeyValuePair<string, object> item in colValues)
                    {
                        SQLiteParameter p = new SQLiteParameter();
                        p.ParameterName = "$" + item.Key;
                        p.Value = item.Value;
                        cmd.Parameters.Add(p);

                        sb.AppendFormat(" and {0}=${0}", item.Key);
                    }
                }
                cmd.CommandText = sb.ToString();
                object re = cmd.ExecuteScalar();
                re = Convert.ChangeType(re, typeof(T));
                return (T)re;
            }
        }

        public T GetValueTable<T>(string table, string colName, Dictionary<string, object> colValues,int time)
        {
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("select {0} from {1}", colName, table);
                cmd.CommandType = CommandType.Text;
                switch (time)
                {
                    case 1:
                        sb.Append(string.Format(" where Date>='{0}'",DateTime.Now.ToString("yyyy-MM-dd"))); break;//每天
                    case 2:
                        sb.Append(string.Format(" where Date>='{0}'", DateTime.Now.ToString("yyyy-MM"))); break;//每月
                    default: 
                        sb.Append(" where 1=1"); break;
                }
                if (colValues != null)
                {
                    foreach (KeyValuePair<string, object> item in colValues)
                    {
                        SQLiteParameter p = new SQLiteParameter();
                        p.ParameterName = "$" + item.Key;
                        p.Value = item.Value;
                        cmd.Parameters.Add(p);

                        sb.AppendFormat(" and {0}=${0}", item.Key);
                    }
                }
                //MessageBox.Show(sb.ToString());
                cmd.CommandText = sb.ToString();
                object re = cmd.ExecuteScalar();
                re = Convert.ChangeType(re, typeof(T));
                return (T)re;
            }
        }
        #endregion

        #region == GetList ==
        public DataTable GetList(string sql)
        {
            return GetList(sql, null);
        }

        public DataTable GetList(string sql, string paramName, object paramValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(paramName, paramValue);
            return this.GetList(sql, pm);
        }

        public DataTable GetList(string sql, Dictionary<string, object> paramValues)
        {
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;

                if (paramValues != null)
                {
                    foreach (KeyValuePair<string, object> item in paramValues)
                    {
                        SQLiteParameter p = new SQLiteParameter();
                        p.ParameterName = "$" + item.Key;
                        p.Value = item.Value;
                        cmd.Parameters.Add(p);
                    }
                }

                SQLiteDataAdapter Da = new SQLiteDataAdapter();
                Da.SelectCommand = cmd;
                DataTable Dtb = new DataTable();
                Da.Fill(Dtb);
                return Dtb;
            }
        }
        #endregion

        #region == Insert ==
        public int Insert(string tableName, Dictionary<string, object> colvalues)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" insert into {0} (", tableName);
            string flag = "";
            foreach (KeyValuePair<string, object> item in colvalues)
            {
                sb.AppendFormat("{0}{1}", flag, item.Key);
                flag = ",";
            }
            sb.Append(") Values (");
            flag = "";
            foreach (KeyValuePair<string, object> item in colvalues)
            {
                sb.AppendFormat("{0}${1}", flag, item.Key);
                flag = ",";
            }
            sb.Append(")");
            return this.Excute(sb.ToString(), colvalues);
        }
        #endregion

        #region == Update ==
        public int Update(string table, Dictionary<string, object> colValues, Dictionary<string, object> where)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" update {0} set ", table);
            string flag = "";
            foreach (KeyValuePair<string, object> item in colValues)
            {
                sb.AppendFormat("{0}{1}={2}{1}", flag, item.Key, "$");
                flag = ",";
                pm.Add(item.Key, item.Value);
            }

            flag = " where ";
            foreach (KeyValuePair<string, object> item in where)
            {
                sb.AppendFormat("{0}{1}=${1}", flag, item.Key);
                flag = " and ";
                pm.Add(item.Key, item.Value);
            }
            return this.Excute(sb.ToString(), pm);
        }

        public int Update(string table, Dictionary<string, object> colvalues, string pkcol, object pkValue)
        {
            Dictionary<string, object> where = new Dictionary<string, object>();
            where.Add(pkcol, pkValue);

            return this.Update(table, colvalues, where);
        }

        public int Update(string table, string colName, object colNameValue, string colWhere, object colWhereValue)
        {
            Dictionary<string, object> where = new Dictionary<string, object>();
            where.Add(colWhere, colWhereValue);

            Dictionary<string, object> colvalues = new Dictionary<string, object>();
            colvalues.Add(colName, colNameValue);

            return this.Update(table, colvalues, where);
        }

        #endregion

        #region == Delete ==
        public int Delete(string table, string pkColumn, params object[] values)
        {
            Dictionary<string, object> pam = new Dictionary<string, object>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("delete from {0} where {1}", table, pkColumn);

            if (values.Length == 1)
            {
                sb.AppendFormat("={0}{1}", "$", pkColumn);
                pam.Add(pkColumn, values[0]);
            }
            else
            {
                sb.Append(" in (");
                string flag = "";
                for (int i = 0; i < values.Length; i++)
                {
                    sb.AppendFormat("{0}{1}{2}{3}", flag, "$", pkColumn, i);
                    pam.Add(pkColumn + i, values[i]);
                    flag = ",";
                }
                sb.Append(")");
            }

            return this.Excute(sb.ToString(), pam);
        }

        public int Delete(string table, Dictionary<string, object> colValues)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("delete from {0}", table);

            if (colValues != null && colValues.Count > 0)
            {
                sb.Append(" where 1=1");
                foreach (KeyValuePair<string, object> item in colValues)
                {
                    sb.AppendFormat(" and {0}={1}{0}", item.Key, "$");
                }
            }
            return this.Excute(sb.ToString(), colValues);
        }

        #endregion

        #region == Get ==
        public DmRecord Get(string table, string pkColumn, object value)
        {
            Dictionary<string, object> pam = new Dictionary<string, object>();
            pam.Add(pkColumn, value);

            return this.Get(table, pam);
        }

        public DmRecord Get(string table, Dictionary<string, object> colValues)
        {
            string sql = string.Format("select * from {0} where 1=1", table);
            foreach (KeyValuePair<string, object> item in colValues)
            {
                sql += string.Format(" and {0}{1}={1}", "$", item.Key);
            }
            return new DmRecord(this.GetList(sql, colValues));
        }
        #endregion

        #region == Count ==
        public int Count(string table)
        {
            return this.Count(table, null);
        }

        public int Count(string table, string colName, object colValue)
        {
            Dictionary<string,object> pm = new Dictionary<string,object>();
            pm.Add(colName, colValue);

            return this.Count(table, pm);
        }

        public int Count(string table, Dictionary<string, object> colValues)
        {
            return this.GetValueTable<int>(table, "COUNT(1)", colValues);
        }

        public int Count(string table, Dictionary<string, object> colValues, int time)
        {
            return this.GetValueTable<int>(table, "COUNT(1)", colValues, time);
        }

        public int CountSql(string sql)
        {
            return this.CountSql(sql, null);
        }

        public int CountSql(string sql, string paramName, object paramValue)
        {
            Dictionary<string, object> pm = new Dictionary<string, object>();
            pm.Add(paramName, paramValue);

            return this.CountSql(sql, pm);
        }

        public int CountSql(string sql, Dictionary<string, object> paramValues)
        {
            return this.GetValue<int>(string.Format("select COUNT(1) from ({0}) tb", sql), paramValues);
        }
        #endregion

        #region == Exists ==
        public bool Exists(string tb)
        {
            return this.Count(tb) > 0;
        }

        public bool Exists(string tb, string colName, object value)
        {
            return this.Count(tb, colName, value) > 0;
        }

        public bool Exists(string tb, Dictionary<string, object> paramValues)
        {
            return this.Count(tb, paramValues) > 0;
        }

        public bool ExistsSql(string sql)
        {
            return this.CountSql(sql) > 0;
        }

        public bool ExistsSql(string sql, string paramName, object paramValue)
        {
            return this.CountSql(sql, paramName, paramValue) > 0;
        }

        public bool ExistsSql(string sql, Dictionary<string, object> paramValues)
        {
            return this.CountSql(sql, paramValues) > 0;
        }
        #endregion

        #region == Page ==
        /// <param name="pageNum">页码，从1开始</param>
        public DataTable Page(string sql, int pageNum, int pageSize, ref int recordCount)
        {
            return this.Page(sql, null, pageNum, pageSize, ref recordCount);
        }

        /// <param name="pageNum">页码，从1开始</param>
        public DataTable Page(string sql, Dictionary<string,object> paramValues, int pageNum, int pageSize, ref int recordCount)
        {
            recordCount = this.CountSql(sql, paramValues);
            DataTable dt = this.GetList(sql + string.Format(" limit {0},{1}", pageSize * (pageNum - 1), pageSize), paramValues);
            return dt;
        }
        #endregion
    }
}
